﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using XT.Exam.BLL;
using XT.Exam.Common;
using System.Data.SqlClient;
using XT.Exam.MODEL;
using XT.Exam.IBLL;
using XT.Exam.IDataAccess;
using Newtonsoft.Json;
using System.Data.OleDb;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Configuration;
namespace ExamSystemMvc.Controllers
{
    public class SysUserInfoController : Controller
    {
        //
        // GET: /SysUserInfo/
       #region ///个人信息
        public string II()
        {
            var userinfo = Session["userinfo"] as SystemUserInfo;
            var str = JsonConvert.SerializeObject(userinfo);
            return str;
        }
        #endregion        
        #region  ///首页
        public ActionResult Index()
        {
            return View();
        }
        #endregion
        #region   欢迎界面
        public ActionResult Welcome()
        {
            return View();
        }
        #endregion
        #region 学生列表管理
        /// <summary>
        /// 学生列表管理
        /// </summary>
        /// <returns></returns>
        public ActionResult StudentShow()
        {
            SysStudentBLL student = new SysStudentBLL();
            List<Student> list = student.GetList<Student>();
            return View(list);
        }
        #endregion
        #region ///组卷策略


        /// <summary>
        /// 开始组卷
        /// </summary>
        /// <returns></returns>
        public ActionResult TestPaper()
        {

            return View();
        }
        [HttpPost]
        public ActionResult AddStrategy(string CollegeID, string TieID, string PhaseID, string TPStrategy_Name, int RadioCount, decimal RadioScore, int CheckCount, decimal CheckScore, int JudgeCount, decimal JudgeScores, int reault)
        {
            TPStrategy tp = new TPStrategy();
            SqlParameter[] parameter =
            {
                 new SqlParameter("@ID","NEWID()"),
                        new SqlParameter("@TPStrategy_Name",TPStrategy_Name),
                        new SqlParameter("@TotalScore",reault),
                        new SqlParameter("@RadioCount",RadioCount),
                         new SqlParameter("@RadioScore",RadioScore),
                        new SqlParameter("@CheckCount",CheckCount),
                        new SqlParameter("@CheckScore",CheckScore),
                         new SqlParameter("@JudgeCount",JudgeCount),
                        new SqlParameter("@JudgeScores",JudgeScores),
                        new SqlParameter("@SystemUserInfoID",1),
                        new SqlParameter("@CreateTime",DateTime.Now),

                 };



            ITPStrategyBuisnessLogic itp = new TPStrategyBuisnessLogic();

            if (itp.Add(tp, parameter))
            {
                return Content("<script>alert('添加组卷略率成功');location.href='/SysUserInfo/TestPaperList'</script>");
            }
            else
            {
                return Content("<script>alert('添加组卷略率失败，请重新添加')</script>");
            }

        }

        /// <summary>
        /// 策略一览
        /// </summary>
        /// <returns></returns>
        public ActionResult TestPaperList()
        {
            ITPStrategyBuisnessLogic itp = new TPStrategyBuisnessLogic();
            return View(itp.GetList<TPStrategy>());
        }
        /// <summary>
        /// 修改策略页面
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public ActionResult UpdateTestPaPer(string ID)
        {
            return View();
        }
        /// <summary>
        /// 删除策略
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int DeleteTestPaPer()
        {
            TPStrategy tp = new TPStrategy();
            tp.ID = new Guid(Request["ID"]);
            ITPStrategyBuisnessLogic itp = new TPStrategyBuisnessLogic();

            if (itp.DeleteById(tp))
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }
        #endregion
        #region ///发布试卷
        /// <summary>
        /// 试卷列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetListPaper()
        {
            IAddTestPaperBuisnessLogic itp = new AddTestPaperBuisnessLogic();
            var list = itp.GetStudentList();
            return View(list);
        }
        /// <summary>
        /// 删除试卷
        /// </summary>
        /// <returns></returns>
        public int DeleteAddTestPaPer()
        {
            TestPaper tp = new TestPaper();
            tp.ID = new Guid(Request["ID"]);
            ITestPaperBuisnessLogic itp = new TestPaperBuisnessLogic();
            if (itp.DeleteById(tp))
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 添加试卷
        /// </summary>
        /// <returns></returns>
        public ActionResult AddTestPaper(Guid ID)
        {
            ViewBag.guid = ID;
            IUnitTableBuisnessLogic unit = new UnitTableBuisnessLogic();
            ViewBag.unit = unit.GetList<UnitTable>();
            return View();
        }


        public ActionResult AddTestPaperDo(int CollegeID, int TieID, int PhaseID, string TestPaper_Name, int Unit_Name, DateTime BeginExam, Guid StrategyID, int EndTime, bool TestPaper_State, string[] Class_Name)
        {
            var Class_Name1 = string.Join(",", Class_Name);
            
            TestPaper tp = new TestPaper();
            SqlParameter[] parameter =
            {
                 new SqlParameter("@ID","NEWID()"),
                        new SqlParameter("@TestPaper_Name",TestPaper_Name),
                        new SqlParameter("@CollegeID",CollegeID),
                        new SqlParameter("@TieID",TieID),
                         new SqlParameter("@PhaseID",PhaseID),
                        new SqlParameter("@Unit_Name",Unit_Name),
                        new SqlParameter("@BeginExam",BeginExam),
                         new SqlParameter("@EndTime",EndTime),
                        new SqlParameter("@TestPaper_State",TestPaper_State),
                        new SqlParameter("@Class_Name",Class_Name1),
                        new SqlParameter("@SystemUserInfoID",1),
                        new SqlParameter("@CreateTime",DateTime.Now),
                        new SqlParameter("@StrategyID",StrategyID),
                 };

            ITestPaperBuisnessLogic itp = new TestPaperBuisnessLogic();

            if (itp.Add(tp, parameter))
            {
                SqlParameter[] parameter1 =
            {
                        new SqlParameter("@StrategyID",StrategyID),
                 };
                ITPStrategyBuisnessLogic itps = new TPStrategyBuisnessLogic();
                var radiocount= itps.GetList<TPStrategy>().FirstOrDefault().RadioCount;
                var checkcount = itps.GetList<TPStrategy>().FirstOrDefault().CheckCount;
                var judgecount = itps.GetList<TPStrategy>().FirstOrDefault().JudgeCount;
                SqlParameter[] parameter2 =
            {
                        new SqlParameter("@TestPaper_Name",TestPaper_Name),
                 };
                var id = itp.GetList<TestPaper>(parameter2).FirstOrDefault().ID;

                string connstring = "Data Source=DESKTOP-FNO0VG6;Initial Catalog=考试系统;Persist Security Info=True;User ID=sa;Password=sa";
                try
                {
                    using (SqlConnection conn = new SqlConnection(connstring))
                    {
                        if (conn.State != ConnectionState.Open)
                        {
                            conn.Open();
                        }
                        SqlCommand cmd = new SqlCommand();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Teaparger";
                        cmd.Parameters.Add(new SqlParameter("@RadioCount", radiocount));
                        cmd.Parameters.Add(new SqlParameter("@CheckCount", checkcount));
                        cmd.Parameters.Add(new SqlParameter("@JudgeCount", judgecount));
                        cmd.Parameters.Add(new SqlParameter("@TestPaperID", id));
                        cmd.Parameters.Add(new SqlParameter("@PhaseID", PhaseID));
                        cmd.Parameters.Add(new SqlParameter("@UnitID", Unit_Name));

                        cmd.Connection = conn;
                        var i= cmd.ExecuteNonQuery();
                        if(i<0)
                        {
                            return Content("<script>alert('试卷试题填入错误')</script>");
                        }
                    }
                }
                catch (SqlException e )
                {

                    throw new Exception(e.Message);
                }
                return Content("<script>alert('添加试卷成功');location.href='/SysUserInfo/GetListPaper'</script>");
            }
            else
            {
                return Content("<script>alert('添加试卷失败，请重新添加')location.href='/SysUserInfo/AddTestPaper'</script>");
            }

        }        
        #endregion
        #region  /// 学院数据
        /// <summary>
        /// 学院
        /// </summary>
        /// <returns></returns>
        public string CollegeList()
        {
            SysCollageBLL collage = new SysCollageBLL();
            List<SystemCollege> list = collage.GetList<SystemCollege>();
            var reault = JsonConvert.SerializeObject(list);
            return reault;
        }
        #endregion
        #region /// 系数据
        /// <summary>
        /// 系
        /// </summary>
        /// <param name="ParentID">学院ID</param>
        /// <returns></returns>
        public string TieList(int ParentID)
        {
            SysTieBLL tie = new SysTieBLL();
            SqlParameter[] parameter = { new SqlParameter("@ParentID", ParentID) };
            List<Tie> list = tie.GetList<Tie>(parameter);
            var reault = JsonConvert.SerializeObject(list);
            return reault;
        }
        #endregion
        #region ///阶段数据
        /// <summary>
        /// 阶段
        /// </summary>
        /// <param name="ParentID">系ID</param>
        /// <returns></returns>
        public string PhaseTableList(int ParentID)
        {
            SysPhaseTableBLL phasetable = new SysPhaseTableBLL();
            SqlParameter[] parameter = { new SqlParameter("@ParentID", ParentID) };
            List<PhaseTable> list = phasetable.GetList<PhaseTable>(parameter);
            var reault = JsonConvert.SerializeObject(list);
            return reault;
        }
        #endregion
        #region /// 添加学生
        /// <summary>
        /// 班级
        /// </summary>
        /// <param name="ParentID"></param>
        /// <returns></returns>
        public string ClassTableList(int ParentID)
        {
            IClassTableBuisnessLogic classtable = new ClassTableBuisnessLogic();
            SqlParameter[] parameter = { new SqlParameter("@PhaseID", ParentID) };
            List<ClassTable> list = classtable.GetList<ClassTable>(parameter);
            var reault = JsonConvert.SerializeObject(list);
            return reault;
        }
        /// <summary>
        /// 添加学生
        /// </summary>
        /// <param name="stu">学生对象</param>
        /// <returns></returns>
        public ActionResult AddStudent(Student stu)
        {
            stu.Student_Pass = MD5DBhelper.MD5Encrypt32("000000");
            SysStudentBLL student = new SysStudentBLL();
            SqlParameter[] parameter =
            {
                new SqlParameter("@Student_Numb",stu.Student_Numb),
                new SqlParameter("@Student_Name",stu.Student_Name),
                new SqlParameter("@Student_Sex",stu.Student_Sex),
                new SqlParameter("@Student_Email",stu.Student_Email),
                new SqlParameter("@Student_Tel",stu.Student_Tel),
                new SqlParameter("@Student_Card",stu.Student_Card),
                new SqlParameter("@RoleID",1),
            };
            bool reault = student.Add(stu, parameter);
            return View();
        }
        #endregion
        #region /// 上传导入学生视图
        /// <summary>
        /// 上传导入学生视图
        /// </summary>
        /// <returns></returns>
        public ActionResult leadingin()
        {
            return View();
        }
        #endregion
        #region /// 开始预览
        /// <summary>
        /// 上传
        /// </summary>
        /// <param name="file">Excel文件路径地址</param>
        /// <returns></returns>
        [HttpPost]
        //HttpPostedFileBase：充当类的基类，这些类提供对客户端已上载的单独文件的访问
        public ActionResult Browse(HttpPostedFileBase file)
        {
            if (string.Empty.Equals(file.FileName) || ".xls" != Path.GetExtension(file.FileName))
            {
                throw new ArgumentException("当前文件格式不正确,请确保正确的Excel文件格式!");
            }
            var severPath = this.Server.MapPath("/files/"); //获取当前虚拟文件路径
            var savePath = Path.Combine(severPath, file.FileName); //拼接保存文件路径
            try
            {
                file.SaveAs(savePath);
                DataTable dt = this.ImportExcel(savePath);
                List<Student> list = new List<Student>();
                foreach (DataRow item in dt.Rows)
                {
                    list.Add(new Student()
                    {
                        Student_Numb = item["学生学号"].ToString(),
                        Student_Pass = item["密码"].ToString(),
                        Student_Name = item["姓名"].ToString(),
                        Student_Email = item["邮箱"].ToString(),
                        Student_Tel = item["手机号"].ToString(),
                        Student_Card = item["身份证号"].ToString(),
                        Student_Sex = item["性别"].ToString(),
                        Lecturer_Name = item["讲师"].ToString(),
                        Class_Name = item["班级"].ToString(),
                    });
                }
                ViewBag.list = list;
                return View("leadingin");
            }
            finally
            {
                System.IO.File.Delete(savePath);//每次上传完毕删除文件
            }
        }
        #endregion
        #region /// 导入学生信息到数据库
        /// <summary>
        /// 导入学生信息到数据库
        /// </summary>
        /// <returns></returns>
        public int AddAll()
        {
            List<Student> list = (List<Student>)Session["list"];
            string connstring = "Data Source=DESKTOP-FNO0VG6;Initial Catalog=考试系统;Persist Security Info=True;User ID=sa;Password=sa";
            using (SqlConnection sql = new SqlConnection(connstring))
            {
                //打开数据库
                sql.Open();
                //连接数据库字符串
                string str = "";
                foreach (var item in list)
                {
                    str += @"INSERT INTO [考试系统].[dbo].[Student]([Student_Numb],[Student_Name]
,[Student_Pass],[Student_Email],[Student_Tel],[Student_Card],[Student_Sex],[Lecturer_Name],[Class_Name],[RoleID])VALUES('" + item.Student_Numb + "','" + item.Student_Name + "','" + MD5DBhelper.MD5Encrypt32(item.Student_Pass) + "','" + item.Student_Email + "','" + item.Student_Tel + "','" + item.Student_Card + "','" + item.Student_Sex + "','" + item.Lecturer_Name + "','" + item.Class_Name + "',1)" + ";";
                }
                //截取最后一个字符
                str = str.Substring(0, str.Length - 1);
                SqlCommand cmd = new SqlCommand(str, sql);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return cmd.ExecuteNonQuery();
                }
                else
                    return 0;
            }
        }
        #endregion
        #region  /// 题库列表
        /// <summary>
        /// 题库列表
        /// </summary>
        /// <returns></returns>
        public ActionResult QuestionBankList(int PageIndex = 1, int CollegeID = 0, int TieID = 0, int PhaseID = 0, int iD=0)
        {
            int UnitID = iD;
            int PageSize = 13;
            SysUnitBLL unit = new SysUnitBLL();
            List<UnitTable> UnitTablelist = unit.GetList<UnitTable>();
            ViewBag.ID = new SelectList(UnitTablelist, "ID", "Unit_Name");

            using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=考试系统;Persist Security Info=True;User ID=sa;Password=sa"))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Paged", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sdap = new SqlDataAdapter(cmd);       
                SqlParameter[] parameter =
               {
                    new SqlParameter("@CollegeID",SqlDbType.Int),
                    new SqlParameter("@TieID",SqlDbType.Int),
                    new SqlParameter("@PhaseID",SqlDbType.Int),
                    new SqlParameter("@UnitID",SqlDbType.Int),
                    new SqlParameter("@PageIndex",SqlDbType.Int),
                    new SqlParameter("@PageSize",SqlDbType.Int),
                    new SqlParameter("@Total",SqlDbType.Int),
               };
                parameter[0].Value = CollegeID;
                parameter[1].Value = TieID;
                parameter[2].Value = PhaseID;
                parameter[3].Value = UnitID;
                parameter[4].Value = PageIndex;
                parameter[5].Value = PageSize;
                parameter[6].Direction =  ParameterDirection.Output;
                cmd.Parameters.AddRange(parameter);
                DataTable dt = new DataTable();
                sdap.Fill(dt);
                List<BankList> _blist = new List<BankList>();
                foreach (DataRow item in dt.Rows)
                {
                    BankList _back = new BankList();
                    _back.ID = item["ID"].ToString();
                    _back.Questionbank_Name = item["Questionbank_Name"].ToString();
                    _back.College_Name = item["College_Name"].ToString();
                    _back.Tie_Name = item["Tie_Name"].ToString();
                    _back.Phase_Name = item["Phase_Name"].ToString();
                    _back.ExamType_Name = item["ExamType_Name"].ToString();
                    _back.Unit_Name = item["Unit_Name"].ToString();
                    _blist.Add(_back);
                }
                var Total = Convert.ToInt32(cmd.Parameters["@Total"].Value);
                var PageList = new PagedList<BankList>()
                {
                    list = _blist,
                    CurrentPageIndex = PageIndex,
                    PageSize = PageSize,
                    TotalItemCount = Total,
                    TotalPageCount = Total % PageSize > 0 ? (Total / PageSize) + 1 : Total / PageSize
                };
                Session["list"] = PageList.list;
                Session["CurrentPageIndex"] = PageList.CurrentPageIndex;
                Session["PageSize"] = PageList.PageSize;
                Session["TotalItemCount"] = PageList.TotalItemCount;
                Session["TotalPageCount"] = PageList.TotalPageCount;
                return View(PageList);
            }
            
        }
        #endregion
        #region    /// 导入题视图
        /// <summary>
        /// 导入题视图
        /// </summary>
        /// <returns></returns>
        public ActionResult ToLeadQuestion()
        {
            SysUnitBLL bll = new SysUnitBLL();
            var list2 = bll.GetList<UnitTable>();
            ViewBag.ID = new SelectList(list2, "ID", "Unit_Name");
            return View();
        }
        #endregion
        #region /// 执行导入题NOPI
        /// <summary>
        /// 执行导入题NOPI    预览
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult ToLeadList(HttpPostedFileBase file)
        {
            if (string.Empty.Equals(file.FileName) || ".xls" != Path.GetExtension(file.FileName))
            {
                return Content("<script>alert('当前文件格式不正确,请确保正确的Excel文件格式!')</script>");
            }
            var severPath = this.Server.MapPath("/files/"); //获取当前虚拟文件路径
            var savePath = Path.Combine(severPath, file.FileName); //拼接保存文件路径
            try
            {
                file.SaveAs(savePath);
                DataTable dt = this.ImportExcel(savePath);
                List<XT.Exam.MODEL.QuestionShow> list = new List<XT.Exam.MODEL.QuestionShow>();
                foreach (DataRow item in dt.Rows)
                {
                    XT.Exam.MODEL.QuestionShow model = new XT.Exam.MODEL.QuestionShow();
                    model.ExamType_Name = item["题型"].ToString();
                    model.Questionbank_Name = item["题目"].ToString();
                    model.Options_Count = item["答案"].ToString();
                    model.Rightkey = item["是否正确"].ToString();
                    list.Add(model);
                }
                ViewBag.list = list;
                SysUnitBLL bll = new SysUnitBLL();
                var list2 = bll.GetList<UnitTable>();
                ViewBag.ID = new SelectList(list2, "ID", "Unit_Name");
                return View("ToLeadQuestion");
            }
            finally
            {
                System.IO.File.Delete(savePath);//每次上传完毕删除文件
            }
        }
        #endregion
        #region  ///执行导入题到数据库中
        /// <summary>
        ///执行导入题到数据库中
        /// </summary>
        /// <param name="College">学院编号</param>
        /// <param name="Tie">系编号</param>
        /// <param name="phasetable">阶段编号</param>
        /// <param name="Unit">单元编号</param>
        /// <returns></returns>
        public int QuestionAll(int College, int Tie, int phasetable, int Unit)
        {
            List<XT.Exam.MODEL.QuestionShow> list = (List<XT.Exam.MODEL.QuestionShow>)Session["Questionlist"];
            try
            {
                SystemUserInfo userinfo = Session["userinfo"] as SystemUserInfo;
                string sql = "";
                string s = "";
                for (int i = 0; i < list.Count; i++)
                {
                    int ExamType = 0;
                    if (list[i].ExamType_Name.Trim() == "单选题")
                    { ExamType = 1; }
                    else if (list[i].ExamType_Name.Trim() == "多选题")
                    { ExamType = 2; }
                    else { ExamType = 3; }
                    string Questionbank_Name = list[i].Questionbank_Name;//提干
                    string ExamType_Name = list[i].ExamType_Name;//提干类型
                    string Options_Count = list[i].Options_Count;//选项内容
                    if (list[i].Rightkey != "")
                    {
                        int Rightkey = Convert.ToInt32(list[i].Rightkey);//是否正确答案
                    }
                    if (Questionbank_Name != "" && ExamType_Name != "")
                    {
                        Guid guid = Guid.NewGuid();
                        s = guid.ToString();
                        sql += @"INSERT INTO [考试系统].[dbo].[Questionbank]([ID],[Questionbank_Name],[ExamTypeID],[CollegeID],[TieID],[PhaseID],[UnitID],[SystemUserInfoID],[CreateTime])VALUES('" + guid + "','" + list[i].Questionbank_Name + "'," + ExamType + "," + College + "," + Tie + "," + phasetable + "," + Unit + "," + userinfo.ID + ",getdate())" + ";";
                    }
                    else
                    {
                        sql += "INSERT INTO [考试系统].[dbo].[Options]([ID],[Options_Count],[Rightkey],[QuestionbankID])VALUES(newID(),'" + list[i].Options_Count + "'," + list[i].Rightkey + ",'" + s + "')" + ";";
                    }
                }
                sql = sql.Substring(0, sql.Length - 1);
                using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=考试系统;Persist Security Info=True;User ID=sa;Password=sa"))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                  throw  ex ;
            }
        }
        #endregion
        #region   /// 题型列表视图
        /// <summary>
        /// 题型列表视图
        /// </summary>
        /// <returns></returns>
        public ActionResult ExamTypeList()
        {
            return View();
        }
        #endregion
        #region ///导入辅助
        /// <summary>
        /// 导入辅助
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellValue(NPOI.SS.UserModel.ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator hfs = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        hfs.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch (Exception)
                    {

                        return cell.NumericCellValue.ToString();
                    }
            }
        }
        #endregion
        #region /// 导入数据辅助
        /// <summary>
        /// 导入数据辅助
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataTable ImportExcel(string filePath)
        {
            HSSFWorkbook hssfworkbook;
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception err)
            {

                throw err;
            }
            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
            DataTable dt = new DataTable();
            IRow headerrow = sheet.GetRow(0);  //获取标题行数据
            int cell = headerrow.LastCellNum;
            int row = sheet.LastRowNum;
            for (int i = headerrow.FirstCellNum; i < cell; i++)
            {
                DataColumn column = new DataColumn(headerrow.GetCell(i).StringCellValue);
                dt.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i <= row; i++)
            {
                IRow rows = sheet.GetRow(i);
                DataRow datarow = dt.NewRow();
                if (rows != null)
                {
                    for (int j = rows.FirstCellNum; j < cell; j++)
                    {
                        if (rows.GetCell(j) != null)
                        {
                            datarow[j] = GetCellValue(rows.GetCell(j));
                        }
                    }
                }
                dt.Rows.Add(datarow);
            }
            return dt;
        }
        #endregion

    }
}
